clear all
cap log close
do "$dofiles/02_model\01_pre_est/readin/income_hours_2_readin"
do "$dofiles/02_model\01_pre_est/readin/income_hours_2_format"

//generation of uniqid varaible
gen uniqid = ER30001*1000 + ER30002 //generation of uniqid identifier variable
duplicates report uniqid //no duplicates!
drop ER30002 ER30000 
tempfile inclate
save `inclate'


clear all
cap log close
do "$dofiles/02_model\01_pre_est/readin/income_hours_readin"
do "$dofiles/02_model\01_pre_est/readin/income_hours_format"

//generation of uniqid varaible
gen uniqid = ER30001*1000 + ER30002 //generation of uniqid identifier variable
duplicates report uniqid //no duplicates!
drop ER30002 ER30000 

//rename four problem variables
ren ER24080 hours_head_2002
ren ER24091 hours_spouse_2002
ren ER24116 inc_head_2002
ren ER24135 inc_spouse_2002

merge 1:1 uniqid using `inclate', keep(1 3) nogen



//loop over variables and drop what we can
ds uniqid, not
foreach var in `r(varlist)'{
	
	//fetch label
	local lab: variable label `var'
	
	//kill sequence/release/interview numbers
	if strpos("`lab'", "SEQUENCE") | strpos("`lab'", "RELEASE") | strpos("`lab'", "MARITAL") {
		drop `var'
		continue
	}
		
	if strpos("`lab'", " 6") | strpos("`lab'", " 7") | strpos("`lab'", " 8") | strpos("`lab'", " 9"){
		drop `var'
	}
}

drop V* ER12080 ER12082 ER16463 ER16465 

//manual renaming of remaining variables -- too difficult to figure out how to automate
ren ER20399 hours_head_2000
ren ER20410 hours_spouse_2000
ren ER20443 inc_head_2000
ren ER20447 inc_spouse_2000
ren ER33601 fam_2001
ren ER33603 relate_2001
ren ER23702D3 weeks_worked_head_2001
ren ER23702E8 hours_week_head_2001
ren ER23702F1 inc_head_2001
ren ER23702J6 weeks_worked_spouse_2001
ren ER23702L2 hours_week_spouse_2001
ren ER23702L4 inc_spouse_2001
ren ER33701 fam_2003
ren ER33703 relate_2003
ren ER27711D3 weeks_worked_head_2003
ren ER27711E8 hours_week_head_2003
ren ER27711F1 inc_head_2003
ren ER27711J6 weeks_worked_spouse_2003
ren ER27711L2 hours_week_spouse_2003
ren ER27711L4 inc_spouse_2003
ren ER27886 hours_head_2004
ren ER27897 hours_spouse_2004
ren ER27931 inc_head_2004
ren ER27943 inc_spouse_2004
ren ER33801 fam_2005
ren ER33803 relate_2005
ren ER40686D3 weeks_worked_head_2005
ren ER40686E8 hours_week_head_2005
ren ER40686F1 inc_head_2005
ren ER40686J6 weeks_worked_spouse_2005
ren ER40686L2 hours_week_spouse_2005
ren ER40686L4 inc_spouse_2005
ren ER40876 hours_head_2006
ren ER40887 hours_spouse_2006
ren ER40921 inc_head_2006
ren ER40933 inc_spouse_2006
ren ER33901 fam_2007
ren ER33903 relate_2007
ren ER46670 weeks_worked_head_2007
ren ER46671 hours_week_head_2007
ren ER46673 inc_head_2007
ren ER46681 weeks_worked_spouse_2007
ren ER46682 hours_week_spouse_2007
ren ER46684 inc_spouse_2007
ren ER46767 hours_head_2008
ren ER46788 hours_spouse_2008
ren ER46829 inc_head_2008
ren ER46841 inc_spouse_2008
ren ER34001 fam_2009
ren ER34003 relate_2009
ren ER52071 weeks_worked_head_2009
ren ER52072 hours_week_head_2009
ren ER52074 inc_head_2009
ren ER52082 weeks_worked_spouse_2009
ren ER52083 hours_week_spouse_2009
ren ER52085 inc_spouse_2009
ren ER52175 hours_head_2010
ren ER52196 hours_spouse_2010
ren ER52237 inc_head_2010
ren ER52249 inc_spouse_2010
ren ER34101 fam_2011
ren ER34103 relate_2011
ren ER57825 weeks_worked_head_2011
ren ER57839 hours_week_head_2011
ren ER57841 inc_head_2011
ren ER57873 weeks_worked_spouse_2011
ren ER57887 hours_week_spouse_2011
ren ER57889 inc_spouse_2011
ren ER57976 hours_head_2012
ren ER57997 hours_spouse_2012
ren ER58038 inc_head_2012
ren ER58050 inc_spouse_2012
ren ER34201 fam_2013
ren ER34203 relate_2013
ren ER65005 weeks_worked_head_2013
ren ER65019 hours_week_head_2013
ren ER65021 inc_head_2013
ren ER65053 weeks_worked_spouse_2013
ren ER65067 hours_week_spouse_2013
ren ER65069 inc_spouse_2013
ren ER65156 hours_head_2014
ren ER65177 hours_spouse_2014
ren ER65216 inc_head_2014
ren ER65244 inc_spouse_2014
ren ER34301 fam_2015
ren ER34303 relate_2015
ren ER71097 weeks_worked_head_2015
ren ER71111 hours_week_head_2015
ren ER71113 inc_head_2015
ren ER71145 weeks_worked_spouse_2015
ren ER71159 hours_week_spouse_2015
ren ER71161 inc_spouse_2015
ren ER71233 hours_head_2016
ren ER71254 hours_spouse_2016
ren ER71293 inc_head_2016
ren ER71321 inc_spouse_2016
ren ER34501 fam_2017
ren ER34503 relate_2017
ren ER66170 weeks_worked_head_2016
ren ER66172 hours_week_head_2016
ren ER66445 weeks_worked_spouse_2016
ren ER66447 hours_week_spouse_2016
ren ER72170 weeks_worked_head_2018
ren ER72172 hours_week_head_2018
ren ER72447 weeks_worked_spouse_2018
ren ER72449 hours_week_spouse_2018
ren ER77119 weeks_worked_head_2017
ren ER77133 hours_week_head_2017
ren ER77135 inc_head_2017
ren ER77167 weeks_worked_spouse_2017
ren ER77181 hours_week_spouse_2017
ren ER77183 inc_spouse_2017
ren ER77255 hours_head_2018
ren ER77276 hours_spouse_2018
ren ER77315 inc_head_2018
ren ER77343 inc_spouse_2018
ren ER34701 fam_2019
ren ER34703 relate_2019


drop ER*

//*****convert to hourly wages. Keep nothing but hours and hourly wages
local years `"2001 2003 2005 2007 2009 2011 2013 2015 2017"'

foreach y in `years'{
	gen hours_head_`y' = hours_week_head_`y' * weeks_worked_head_`y'
	gen hours_spouse_`y' = hours_week_spouse_`y' * weeks_worked_spouse_`y'
}

keep uniqid inc_* hours_head* hours_spouse* fam* relate*


//*****reshape
reshape long inc_head_ inc_spouse_ hours_head_ hours_spouse_ fam_ relate_, i(uniqid) j(year)

*****deal with odd codings
replace inc_head = . if inc_head == 999999
replace inc_head = . if inc_head == 9999999
replace inc_head = . if inc_head == 99999999
replace inc_spouse = . if inc_spouse == 999999
replace inc_spouse = . if inc_spouse == 9999999
replace inc_spouse = . if inc_spouse == 99999999
replace inc_head = . if inc_head<0
replace inc_spouse = . if inc_spouse<0

****generate wage variables
gen wage_head = inc_head / hours_head
gen wage_spouse = inc_spouse / hours_spouse



******Obtain family/relate information from following entry if missing
sort uniqid year
replace fam = fam[_n+1] if fam[_n] == . & fam[_n+1]!=. & uniqid[_n] == uniqid[_n+1]
replace relate = relate[_n+1] if relate[_n] == . & relate[_n+1]!=. & uniqid[_n] == uniqid[_n+1]

*****restrict to a few values of relate variable and standardize
replace relate = 1 if relate == 10
replace relate = 2 if relate == 20
replace relate = 3 if relate == 30
keep if relate == 1 | relate == 2 | relate == 3 | relate == .
drop if relate == . & (year == 2019 | year == 2018)


//*****deflate to 2012 dollars
merge m:1 year using "$data/GDP/gdp_pce_deflator", keep(match) nogen
replace deflator = deflator/100 //normalize
replace wage_head = wage_head/deflator //deflate wages
replace wage_spouse = wage_spouse/deflator //deflate wages

//clean up relate variable
gen temp = relate
drop relate 
ren temp relate

*****construct single income varaible and clean up
gen wage = .
gen hours = .
replace wage = wage_head  if relate == 1 //head labor income
replace wage = wage_spouse  if relate == 2 //spouse labor income
replace hours = hours_head  if relate == 1 //head labor income
replace hours = hours_spouse  if relate == 2 //spouse labor income
ren fam fam
ren relate relate

su wage //sanity check. Average wage = 25. Looks good!
keep uniqid year wage hours fam relate
sort uniqid year
tab year
//drop if year == 2017 //no income observations here, so delete!
drop if year == 2019
save "$temp/psid_hours_wages", replace
//


